package DataBase;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DatasourceManager 
{
	public static DbcpBean dbcp ;
	static{
		dbcp = new DbcpBean();
	}
//	public static int select(String Ds_);
	public static int insert(Datasource ds) //插入
	{
//		DbcpBean dbcp = new DbcpBean();
		Connection conn = dbcp.getConnection();
		int result = 0;
		if(conn != null){
			try {
				Statement stmt = conn.createStatement();
				String sql = "insert into tb_datasource (ds_hostname,ds_IP,ds_port) values ('"
					+ ds.getDs_hostname() + "' , '"+ds.getDs_IP()+"','" 
					+ ds.getDs_port() + "');";
				result  = stmt.executeUpdate(sql);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				try {
					conn.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
				e.printStackTrace();
			}			
		}
		try
		{
			conn.close();
		} catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		return result;
	}

	public static int getAutoKey(Datasource ds){
//		DbcpBean dbcp = new DbcpBean();
		Connection conn = dbcp.getConnection();
		ResultSet result = null;
//		ArrayList<Datasource> dss = new ArrayList<Datasource>();
		int autokey = -1;
		if(conn != null)
		{
			try 
			{
				Statement stmt = conn.createStatement();
				String sql = null;
				sql = "select ds_ID from tb_datasource where ds_hostname = '"
						+ ds.getDs_hostname() + "' and ds_port = "
						+ ds.getDs_port() + "  and ds_IP = '"
						+ ds.getDs_IP() + "';";
				
				result  = stmt.executeQuery(sql);
				if(result.next()){
					autokey = result.getInt("ds_ID");
				}
			} 
			catch (SQLException e) 
			{
				try {
					conn.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
				e.printStackTrace();
			}			
		}
		try 
		{
			conn.close();
		} 
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return autokey;
		
	}
	//
	public static Datasource select(int ds_ID) 
	{
//		DbcpBean dbcp = new DbcpBean();
		Connection conn = dbcp.getConnection();
		ResultSet result = null;
		Datasource ds = new Datasource();
		if(conn != null)
		{
			try 
			{
				Statement stmt = conn.createStatement();
				String sql = null;
				sql = "select * from tb_datasource where ds_ID = '"
					+ ds_ID +"';";

				result  = stmt.executeQuery(sql);
				if(result.next()){
					ds.setDs_ID(result.getInt("ds_ID"));
					ds.setDs_hostname(result.getString("ds_hostname"));
					ds.setDs_IP(result.getString("ds_IP"));
					ds.setDs_port(result.getInt("ds_port"));
				}
			} 
			catch (SQLException e) 
			{
				try {
					conn.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
				e.printStackTrace();
			}			
		}
		try 
		{
			conn.close();
		} 
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ds;
	}
	
	
	
	
	
	public static ArrayList<Datasource> select(String ds_hostname) 
	{
//		DbcpBean dbcp = new DbcpBean();
		Connection conn = dbcp.getConnection();
		ResultSet result = null;
		ArrayList<Datasource> dss = new ArrayList<Datasource>();
		if(conn != null)
		{
			try 
			{
				Statement stmt = conn.createStatement();
				String sql = null;
				if(ds_hostname == "")
				{
					sql = "select * from tb_datasource;";
					
				}
				else
				{
					sql = "select * from tb_datasource where ds_hostname = '"
						+ ds_hostname +"';";

				}
				result  = stmt.executeQuery(sql);
				while(result.next()){
					Datasource ds = new Datasource();
					ds.setDs_ID(result.getInt("ds_ID"));
					ds.setDs_hostname(result.getString("ds_hostname"));
					ds.setDs_IP(result.getString("ds_IP"));
					ds.setDs_port(result.getInt("ds_port"));
					dss.add(ds);
				}
			} 
			catch (SQLException e) 
			{
				try {
					conn.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
				e.printStackTrace();
			}			
		}
		try 
		{
			conn.close();
		} 
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return dss;
	}
	
	
	
	
	
	public static int delete(String ds_hostname) 
	{
//		DbcpBean dbcp = new DbcpBean();
		Connection conn = dbcp.getConnection();
		int result = 0;
		if(conn != null)
		{
			try 
			{
				Statement stmt = conn.createStatement();
				String sql = null;
				if(ds_hostname == "")
				{
					sql = "delete from tb_datasource;";
					
				}
				else
				{
					sql = "delete from tb_datasource where ds_hostname = '"
						+ ds_hostname +"';";
				}
				result  = stmt.executeUpdate(sql);
				
			} 
			catch (SQLException e) 
			{
				try {
					conn.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
				e.printStackTrace();
			}			
		}
		try 
		{
			conn.close();
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return result;
	}
	
	public static int update(String ds_hostname) 
	{
//		DbcpBean dbcp = new DbcpBean();
		Connection conn = dbcp.getConnection();
		int result = 0;
		if(conn != null)
		{
			try 
			{
				Statement stmt = conn.createStatement();
				
				String sql = "update tb_datasource set ds_hostname = 'success' where ds_hostname = '"
					         +ds_hostname +"';";
				result  = stmt.executeUpdate(sql);
			} 
			catch (SQLException e) 
			{
				System.out.println("查询失败！");
				try {
					conn.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
				e.printStackTrace();
			}			
		}
		try 
		{
			conn.close();
		} 
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	
	public static void main(String[] args) 
	{   
//		Datasource ds = new Datasource();
//		ds.setDs_hostname("ahwxr");
//		ds.setDs_IP("192.168.1.1");
		
//		DatasourceManager.insert(ds);//插入
		System.out.println(DatasourceManager.select("ahwxr"));//查询
//		System.out.println(DatasourceManager.getAutoKey(ds));
		
//		DatasourceManager.delete("ahwxr");//删除
//		System.out.println(DatasourceManager.select(""));//查询
//		
//		DatasourceManager.insert(ds);//插入
//		System.out.println(DatasourceManager.select(""));//查询
//		
//		DatasourceManager.update("ahwxr");//更新
//		System.out.println(DatasourceManager.select(""));//查询
	}
}
